package util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.logging.Logger;

import javax.swing.JOptionPane;
import javax.swing.text.html.parser.Entity;

import constant.Constant;
import entity.Parameters;
import entity.TableNameAndType;

public class DBUtils {

	public static Connection getConnection(Parameters parameters) {

		Connection conn = null;

		String url = "";

		String driverClass = "";

		if ("mysql".equals(parameters.getDataBaseTypeVal())) {
			try {

				url = "jdbc:mysql://" + parameters.getDataBaseIpVal() + ":" + parameters.getDataBasePortVal() + "/"
						+ parameters.getDataBaseNameVal()
						+ "?connectTimeout=10000&socketTimeout=10000&serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull";
				driverClass = "com.mysql.cj.jdbc.Driver";

				parameters.setDataBaseUrl(url);

				parameters.setDataBaseDriverClass(driverClass);

				Class.forName(driverClass);

				DriverManager.setLoginTimeout(10);

				conn = DriverManager.getConnection(url, parameters.getDataBaseUserNameVal(),
						parameters.getDataBasePwdVal());


			} catch (Exception e) {
				LoggerUtil.error(e.getMessage());
				JOptionPane.showMessageDialog(Constant.frmv, "数据库连接失败！请检查数据库类型是否选择正确，相关配置是否填写正确！" + e.getMessage(), "错误",
						JOptionPane.ERROR_MESSAGE);
				return null;
			}
		} else if ("oracle".equals(parameters.getDataBaseTypeVal())) {

			try {

				url = "jdbc:oracle:thin:@//" + parameters.getDataBaseIpVal() + ":" + parameters.getDataBasePortVal() + "/"
						+ parameters.getDataBaseNameVal();
				driverClass = "oracle.jdbc.driver.OracleDriver";

				parameters.setDataBaseUrl(url);

				parameters.setDataBaseDriverClass(driverClass);

				Class.forName(driverClass);

				Properties info = new Properties();
				info.put("oracle.net.CONNECT_TIMEOUT", 10000);
				info.put("oracle.jdbc.ReadTimeout", 10000);
				info.put("user", parameters.getDataBaseUserNameVal());
				info.put("password", parameters.getDataBasePwdVal());
				DriverManager.setLoginTimeout(10);
				conn = DriverManager.getConnection(url, info);

			} catch (Exception e) {
				LoggerUtil.error(e.getMessage());
				JOptionPane.showMessageDialog(Constant.frmv, "数据库连接失败！请检查数据库类型是否选择正确，相关配置是否填写正确！" + e.getMessage(), "错误",
						JOptionPane.ERROR_MESSAGE);
				return null;

			}
		} else if ("postgresql".equals(parameters.getDataBaseTypeVal())) {

			try {

				url = "jdbc:postgresql://" + parameters.getDataBaseIpVal() + ":" + parameters.getDataBasePortVal()
						+ "/" + parameters.getDataBaseNameVal() + "?socketTimeout=1&connectTimeout=1&useUnicode=true&characterEncoding=UTF-8";

				driverClass = "org.postgresql.Driver";

				parameters.setDataBaseUrl(url);

				parameters.setDataBaseDriverClass(driverClass);

				Class.forName(driverClass);

				DriverManager.setLoginTimeout(10);

				conn = DriverManager.getConnection(url, parameters.getDataBaseUserNameVal(),
						parameters.getDataBasePwdVal());

			} catch (Exception e) {
				LoggerUtil.error(e.getMessage());
				JOptionPane.showMessageDialog(Constant.frmv, "数据库连接失败！请检查数据库类型是否选择正确，相关配置是否填写正确！" + e.getMessage(), "错误",
						JOptionPane.ERROR_MESSAGE);
				return null;
			}
		}

		return conn;
	}


	/**
	 * 获取表的主键id
	 *
	 * @param parameters
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
	public static List<String> getPrimarykey(Parameters parameters, String tableName) throws Exception {
		List<String> primaryKeyList = new ArrayList<>(3);
		Connection connection = getConnection(parameters);
		if (connection == null) {
			return null;
		}
		DatabaseMetaData metaData = connection.getMetaData();
		String userName = connection.getMetaData().getUserName();
		// 获取主键字段
		ResultSet primaryKeys = metaData.getPrimaryKeys(null, userName, tableName);
		//根据结果集元数据打印内容
		ResultSetMetaData pkmd = primaryKeys.getMetaData();
		while (primaryKeys.next()) {
			for (int i = 1; i <= pkmd.getColumnCount(); i++) {
				//获取主键的名称
				if ("COLUMN_NAME".equalsIgnoreCase(pkmd.getColumnName(i))) {
					primaryKeyList.add(primaryKeys.getString(i));
				}
			}
		}
		return primaryKeyList;
	}

	/**
	 * @param tableNameVal
	 * @param connection
	 * @return
	 */
	private static Map<String, String> getColumnComment(String databaseType, String tableNameVal, Connection connection) {

		PreparedStatement columnListPst = null;
		ResultSet columnListRs = null;

		String sql = "";
		if ("mysql".equals(databaseType)) {
			sql = "show full columns from `" + tableNameVal + "`";
		} else if ("postgresql".equals(databaseType)) {

			String[] tableNameArr = tableNameVal.split("\\.");

			if (tableNameArr.length == 1) {
				tableNameVal = tableNameArr[0];
			} else {
				tableNameVal = tableNameArr[1];
			}

			sql = "SELECT" +
					" A.attname AS \"Field\"," +
					" col_description ( A.attrelid, A.attnum ) AS \"Comment\"" +
					" FROM" +
					" pg_class AS C," +
					" pg_attribute AS A " +
					" WHERE" +
					" C.relname = '" + tableNameVal + "' " +
					" AND A.attrelid = C.oid " +
					" AND A.attnum > 0";
		} else if ("oracle".equals(databaseType)) {
			sql = "select column_name AS \"Field\",comments AS \"Comment\" from user_col_comments where lower(table_Name)='" + tableNameVal.toLowerCase() + "'";
		}

		// 列名集合
		Map<String, String> commentMqp = new HashMap<>();
		try {
			columnListPst = connection.prepareStatement(sql);
			columnListRs = columnListPst.executeQuery();

			while (columnListRs.next()) {

				commentMqp.put(columnListRs.getString("Field").toUpperCase(), columnListRs.getString("Comment"));

			}

		} catch (SQLException e) {
			LoggerUtil.error(e.getMessage());
			return null;
		} finally {
			try {
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException ignored) {
			}
			try {
				if (columnListPst != null) {
					columnListPst.close();
				}
			} catch (SQLException ignored) {
			}
			try {
				if (columnListRs != null) {
					columnListRs.close();
				}
			} catch (SQLException ignored) {

			}
		}
		return commentMqp;
	}

	public static List<String> getColumnNameList(String databaseType, String tableNameVal, Connection connection) {

		PreparedStatement columnListPst = null;
		ResultSet columnListRs = null;

		String sql = "";
		if ("mysql".equals(databaseType)) {
			sql = "select * from `" + tableNameVal + "` where 1=0";
		} else if ("oracle".equals(databaseType)) {
			sql = "select * from \"" + tableNameVal + "\" where 1=0";
		} else if ("postgresql".equals(databaseType)) {
			sql = "select * from " + tableNameVal + " where 1=0";
		}

		// 列名集合
		List<String> columnList = new ArrayList<>();

		try {
			columnListPst = connection.prepareStatement(sql);
			columnListRs = columnListPst.executeQuery();
			ResultSetMetaData metaData = columnListRs.getMetaData();

			int columnCount = metaData.getColumnCount();

			for (int i = 0; i < columnCount; i++) {
				columnList.add(metaData.getColumnName(i + 1));
			}

		} catch (SQLException e) {
			LoggerUtil.error(e.getMessage());
			return null;
		} finally {
			try {
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
			}
			try {
				if (columnListPst != null) {
					columnListPst.close();
				}
			} catch (SQLException e) {
			}
			try {
				if (columnListRs != null) {
					columnListRs.close();
				}
			} catch (SQLException e) {

			}
		}
		return columnList;
	}


	/**
	 * 获取表中所有字段名称和类型
	 *
	 * @return
	 */
	public static List<TableNameAndType> getColumnNameAndTypes(String databaseType, String tableNameVal,
															   Connection connection) {

		PreparedStatement columnListPst = null;
		ResultSet columnListRs = null;

		String sql = "";
		if ("mysql".equals(databaseType)) {
			sql = "select * from `" + tableNameVal + "` where 1=0";
		} else if ("oracle".equals(databaseType)) {
			sql = "select * from \"" + tableNameVal + "\" where 1=0";
		} else if ("postgresql".equals(databaseType)) {
			sql = "select * from " + tableNameVal + " where 1=0";
		}

		// 列名集合
		List<TableNameAndType> nameAndTypes = new ArrayList<>();

		try {
			columnListPst = connection.prepareStatement(sql);
			columnListRs = columnListPst.executeQuery();
			ResultSetMetaData metaData = columnListRs.getMetaData();

			int columnCount = metaData.getColumnCount();

			for (int i = 0; i < columnCount; i++) {
				TableNameAndType nameAndType = new TableNameAndType();
				nameAndType.setName(metaData.getColumnName(i + 1));

				//设置sqlParamName
				nameAndType.setSqlParamName(DataUtils.getSqlParam(metaData.getColumnName(i + 1)));

				nameAndType.setComment((metaData.getColumnName(i + 1)));

				String javaTypeName = "";
				String javaClassName = "";
				String columnTypeName = metaData.getColumnTypeName(i + 1).toUpperCase();

				switch (databaseType) {
					case "postgresql":
						switch (columnTypeName) {
							case "VARCHAR":
								javaTypeName = "String";
								break;
							case "CHAR":
								javaTypeName = "String";
								break;
							case "INT2":
								javaTypeName = "Integer";
								break;
							case "INT4":
								javaTypeName = "Integer";
								break;
							case "INT8":
								javaTypeName = "Long";
								break;
							case "SERIAL2":
								javaTypeName = "Integer";
								break;
							case "SERIAL4":
								javaTypeName = "Integer";
								break;
							case "SERIAL8":
								javaTypeName = "Long";
								break;
							case "TEXT":
								javaTypeName = "String";
								break;
							case "DATE":
								javaTypeName = "Date";
								javaClassName = "java.util.Date";
								break;
							case "TIME":
								javaTypeName = "Time";
								javaClassName = "java.util.Date";
								break;
							case "TIMETZ":
								javaTypeName = "Date";
								javaClassName = "java.util.Date";
								break;
							case "TIMESTAMP":
								javaTypeName = "Date";
								javaClassName = "java.util.Date";
								break;
							case "TIMESTAMPTZ":
								javaTypeName = "Date";
								javaClassName = "java.util.Date";
								break;
							default:
								javaTypeName = "String";
								break;
						}
						break;
					case "mysql":
						switch (columnTypeName) {
							case "VARCHAR":
								javaTypeName = "String";
								break;
							case "INT":
								javaTypeName = "Integer";
								break;
							case "CHAR":
								javaTypeName = "String";
								break;
							case "BLOB":
								javaTypeName = "byte[]";
								break;
							case "TEXT":
								javaTypeName = "String";
								break;
							case "INTEGER":
								javaTypeName = "Long";
								break;
							case "TINYINT":
								javaTypeName = "Integer";
								break;
							case "SMALLINT":
								javaTypeName = "Integer";
								break;
							case "MEDIUMINT":
								javaTypeName = "Integer";
								break;
							case "BIT":
								javaTypeName = "Integer";
								break;
							case "BIGINT":
								javaTypeName = "Long";
								break;
							case "FLOAT":
								javaTypeName = "Float";
								break;
							case "DOUBLE":
								javaTypeName = "Double";
								break;
							case "DECIMAL":
								javaTypeName = "BigDecimal";
								javaClassName = "java.math.BigDecimal";
								break;
							case "BOOLEAN":
								javaTypeName = "Integer";
								break;
							// 主键
							case "ID":
								javaTypeName = "Long";
								break;
							case "DATE":
								javaTypeName = "Date";
								javaClassName = "java.util.Date";
								break;
							case "TIME":
								javaTypeName = "Date";
								javaClassName = "java.util.Date";
								break;
							case "DATETIME":
								javaTypeName = "Date";
								javaClassName = "java.util.Date";
								break;
							case "TIMESTAMP":
								javaTypeName = "Date";
								javaClassName = "java.util.Date";
								break;
							case "YEAR":
								javaTypeName = "Date";
								javaClassName = "java.util.Date";
								break;
							default:
								javaTypeName = "String";
								break;
						}
						break;
					case "oracle":
						switch (columnTypeName) {
							case "NUMBER":
								javaTypeName = "Integer";
								break;
							case "DATE":
								javaTypeName = "Date";
								javaClassName = "java.util.Date";
								break;
							case "TIMESTAMP":
								javaTypeName = "Date";
								javaClassName = "java.util.Date";
								break;
							default:
								javaTypeName = "String";
								break;
						}
					default:
						break;
				}

				nameAndType.setTypeName(javaTypeName);
				nameAndType.setClassName(javaClassName);
				nameAndTypes.add(nameAndType);
			}

			//System.out.println(nameAndTypes);

			// 注释map
			Map<String, String> columnComment = getColumnComment(databaseType, tableNameVal, connection);

			for (TableNameAndType tableNameAndType : nameAndTypes) {

				String name = tableNameAndType.getName().toUpperCase();

				// 设置注释内容
				assert columnComment != null;
				tableNameAndType.setComment(
						"".equals(columnComment.get(name)) || columnComment.get(name) == null ? tableNameAndType.getName() : columnComment.get(name));
			}

		} catch (SQLException e) {
			LoggerUtil.error(e.getMessage());
			return null;
		} finally {
			try {

				if (connection != null) {
					connection.close();
				}

			} catch (SQLException ignored) {
			}
			try {
				if (columnListPst != null) {
					columnListPst.close();
				}
			} catch (SQLException ignored) {
			}
			try {
				if (columnListRs != null) {
					columnListRs.close();
				}
			} catch (SQLException ignored) {

			}
		}
		return nameAndTypes;
	}

}
